package Dao.dao;

import Chapter01.DBUtil;
import Chapter01.H20211215.Subject;
import Dao.entity.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

public class StudentDao {
//6：StudentDao中添加如下的查询方法，包含姓名，qq号，手机号的模糊查找，以及分页
//，同时，要考虑如果某个条件为空（null或者空串），则忽略这个查询条件
    public List<Student> search(int pageNo, int pageSize, String sname, String qq, String phone, Date enterStart, Date enterEnd) {
            List<Student> datas = new ArrayList<>();
            Connection connection = DBUtil.getConnection();
            PreparedStatement ps = null;
            ResultSet rs = null;
            List<Object> params=new ArrayList<>();

            String sql = "select * from s_student where 1=1";
            if (sname != null && !sname.equals(""))
            {
                sql += " and sname like concat('%',?,'%')";
                params.add(sname);
            }
            if (qq != null && !qq.equals(""))
            {
                sql += " and qq like concat('%',?,'%')";
                params.add(qq);
            }
            if (phone != null && !phone.equals(""))
            {
                sql += " and telephone like concat('%',?,'%')";
                params.add(phone);
            }
            if(enterStart !=null){
                sql+="and enterdate >?";
                params.add(enterStart);
            }
            if(enterEnd !=null){
                sql+="and enterdate <?";
                params.add(enterEnd);
            }
            sql += " limit ?,?";
            params.add((pageNo - 1) * pageSize);
            params.add(pageSize);
            System.out.println(sql);
            try {
                ps = connection.prepareStatement(sql);
                for (int i=0;i<params.size();i++){
                    ps.setObject(i+1,params.get(i));
                }
                rs = ps.executeQuery();
                while (rs.next()) {
                    Student student = new Student();
                    student.setStuno(rs.getInt("stuno"));
                    student.setSname(rs.getString("sname"));
                    student.setSex(rs.getString("sex"));
                    student.setTelephone(rs.getString("telephone"));
                    student.setQq(rs.getString("qq"));
                    student.setWechat(rs.getString("wechat"));
                    student.setGradeid(rs.getInt("gradeid"));
                    student.setEnterdate(rs.getDate("enterdate"));
                    student.setBirthday(rs.getDate("birthday"));
                    student.setFromcity(rs.getString("fromcity"));
                    student.setAddress(rs.getString("address"));
                    student.setPass(rs.getString("pass"));
                    datas.add(student);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DBUtil.close(rs, ps, connection);
            }
            return datas;
    }

    //增加
    public int add(Student s){
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps=null;
        int zhi=0;
        try {
            System.out.println(connection.getClass().getName());
            System.out.println("连接已经建立");
            String sql="insert into `s_student`(`sname`,`sex`,`telephone`,`qq`,`wechat`,`gradeid`,`enterdate`,`fromcity`,`birthday`,`address`,`pass`)values(?,?,?,?,?,?,?,?,?,?,?)";
            System.out.println(sql);
            ps=connection.prepareStatement(sql);
            //!!给占位符赋值
            ps.setString(1,s.getSname());
            ps.setString(2,s.getSex());
            ps.setString(3,s.getTelephone());
            ps.setString(4,s.getQq());
            ps.setString(5,s.getWechat());
            ps.setLong(6,s.getGradeid());
            ps.setObject(7,s.getEnterdate());
            ps.setString(8,s.getFromcity());
            ps.setObject(9,s.getBirthday());
            ps.setString(10,s.getAddress());
            ps.setString(11,s.getPass());
            zhi=ps.executeUpdate();
            System.out.printf("%d行被修改了",zhi);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(connection);
        }
        return zhi;
    }

    //删除
    public int del( int stuno){
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps=null;
        int zhi=0;
        try {
            System.out.println(connection.getClass().getName());
            System.out.println("连接已经建立");
            String sql="delete from s_student where stuno=?";
            System.out.println(sql);
            ps=connection.prepareStatement(sql);
            //!!给占位符赋值
            ps.setLong(1,stuno);
            zhi=ps.executeUpdate();
            System.out.printf("%d行被修改了",zhi);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(connection);
        }
        return zhi;
    }

    //修改
    public int update(Student s) {
        Connection connection =DBUtil.getConnection();
        PreparedStatement ps = null;
        int zhi=0;
        try {
            System.out.println(connection.getClass().getName());
            System.out.println("连接已经建立");

            String sql = "update s_student set gradeid=?,sname=?,sex=?,qq=?,wechat=?,telephone=?,address=?,fromcity=?,pass=?,birthday=?,enterdate=? where stuno=?";
            System.out.println(sql);
            ps = connection.prepareStatement(sql);
            //!!给占位符赋值
            ps.setLong(1, s.getGradeid());
            ps.setString(2, s.getSname());
            ps.setString(3, s.getSex());
            ps.setString(4, s.getQq());
            ps.setString(5,s.getWechat());
            ps.setString(6,s.getTelephone());
            ps.setString(7,s.getAddress());
            ps.setString(8,s.getFromcity());
            ps.setString(9,s.getPass());
            ps.setDate(10, (Date) s.getBirthday());
            ps.setDate(11, (Date) s.getEnterdate());
            ps.setLong(12,s.getStuno());
            zhi = ps.executeUpdate();
            System.out.printf("%d行被修改了", zhi);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(ps);
            DBUtil.close(connection);
        }
        return zhi;
    }

    //查
    public List<Student> all(){
        List<Student> list=new LinkedList<>();
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps=connection.prepareStatement("select * from s_student");
            rs=ps.executeQuery();
            while (rs.next()){
                Student student=new Student();
                student.setStuno(rs.getLong("stuno"));
                student.setGradeid(rs.getLong("gradeid"));
                student.setSname(rs.getString("sname"));
                student.setSex(rs.getString("sex"));
                student.setQq(rs.getString("qq"));
                student.setWechat(rs.getString("wechat"));
                student.setTelephone(rs.getString("telephone"));
                student.setAddress(rs.getString("address"));
                student.setFromcity(rs.getString("fromcity"));
                student.setPass(rs.getString("pass"));
                student.setBirthday(rs.getDate("birthday"));
                student.setEnterdate(rs.getDate("enterdate"));
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,connection);
        }
        return list;
    }
}
